/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package BD.Modulos;
import BD.Bd;
import BD.ConnBdType;
import Interfaces.BdException;
import Interfaces.UserInterface;
import Objects.Sesion;
import Objects.Usuario;
import Tools.Datos;
import WS.Usuarios;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * 
 * @author armgarces
 */
public class BdUsuarios extends Bd{
    private String mensj;
    /***************************************************************************/
    /********************************** BD *************************************/
    /***************************************************************************/
    /**
     * 
     */
    public BdUsuarios(){       
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectLoadSP(UserInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Usuario":
                return "{call sp_get_user_all}";
            case "Rol":
                return "{call sp_get_rol_all}";
            default:
                return "";
        }
    }    
    /**
     * 
     * @param value
     * @return 
     */
    private String selectFindSP(UserInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Usuario":
                params = new String[]{"@find"};
                return "{call sp_get_user_find(?)}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectCheckSP(int value){
        switch(value){
            case Usuarios.CHECK_USER:
                params = new String[]{"@id_user"};
                return "{call sp_get_user_check(?,?)}";
            default:
                return "";
        }
    }    
    /**
     * 
     * @param value
     * @return 
     */
    private String selectChangeSP(UserInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Usuario":
                mensj = "deleteUser:";
                params = new String[]{"@id_user","@status"};
                return "{call sp_upd_user_status(?,?)}";
            case "Log_TPersonal":
                mensj = "deleteTPersonal:";
                params = new String[]{"@id_tpersonal","@status"};
                return "{call sp_upd_log_tpersonal_status(?,?)}";
            default:
                return "";
        }
    } 
    /***************************************************************************/
    /******************************** SESION ***********************************/
    /***************************************************************************/
    /**
     * 
     * @param usr
     * @param pwd
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public UserInterface iniciarSesion(String usr, String pwd) throws SQLException, BdException {        
        try {
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open(); 
            
            if (connection != null){
                StringBuilder sqlSp = new StringBuilder();
                sqlSp.append("{call sp_get_user_sesion(?, ?)}");
                CallableStatement cstmt = connection.prepareCall(sqlSp.toString());
                cstmt.setString("@id_user", usr);
                cstmt.setString("@pswd", pwd);   
                //
                ResultSet rs = cstmt.executeQuery();                
                if(rs.next() && rs.getObject(1) != null){                                               
                    notificarSesion("Lin", usr, "Inicio Sesion");     
                    Sesion r = new Sesion();
                    return r.loadResultSet(rs);
                }else{                     
                    throw new BdException("Usuario o Contraseña Incorrecta!");
                }   
            }else{
                throw new BdException("Error: Connexion no activa");
            } 
        }catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }             
    }
    /**
     * 
     * @return 
     * @throws java.sql.SQLException
     * @throws Interfaces.BdException
     */
    public boolean cerrarSesion() throws SQLException, BdException{        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                notificarSesion("Lof", "", "Cerrado Forzado de Sesion"); 
                return true;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param usr
     * @return 
     * @throws java.sql.SQLException
     * @throws Interfaces.BdException
     */
    public boolean cerrarSesion(Sesion usr) throws SQLException, BdException{       
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                notificarSesion("Lot",usr.getUsername(),"Cerrar Sesion");      
                return true; 
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }finally{
            connection.close();
        }
    }    
    /**
     * 
     * @param tipo
     * @param usr
     * @param mnsj 
     */
    private void notificarSesion(String tipo,String usr,String mnsj) {
        try{
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_ins_user_sesion(?,?,?,?,?,?)}");            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@tipo_aud", tipo);
               cstmt.setString("@usuario", usr);
               String[] data = Datos.getLocalHost();
               cstmt.setString("@ip_terminal",  data[0]);
               cstmt.setString("@ip_direccion", data[1]);
               cstmt.setString("@ip_mac",       data[2]);
               cstmt.setString("@mensaje", mnsj);
               cstmt.execute();             
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){
//            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
//            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }
    }
    /***************************************************************************/
    /********************************* USERS ***********************************/
    /***************************************************************************/
    /**
     * 
     * @param value
     * @return
     * @throws SQLException
     * @throws BdException 
     */
    public UserInterface[] load(UserInterface value) throws SQLException, BdException{        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();                                
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectLoadSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                ResultSet result = cstmt.executeQuery();                           
                List<UserInterface> list = new ArrayList<>();
                while(result.next()) {
                   UserInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new UserInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param value
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public UserInterface[] find(UserInterface value, String find) throws SQLException, BdException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();                    
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectFindSP(value));           
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                cstmt.setString(params[0], find );
                ResultSet result = cstmt.executeQuery();                           
                List<UserInterface> list = new ArrayList<>();
                while(result.next()) {
                   UserInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new UserInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * Valida si el username generado ya se encuentra asignado a un usuario en la BD
     * @param check
     * @param name valor a ser chequeado en la BD
     * @return true si el username esta en uso, false si esta disponible el username
     * @throws java.sql.SQLException
     * @throws Interfaces.BdException
     */
    public boolean check(int check,String name) throws SQLException, BdException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();                    
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectCheckSP(check));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setString(params[0], name);
                cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
                cstmt.execute();
                return cstmt.getInt("result")==1;               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param user
     * @param value
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean change(String user,UserInterface value) throws SQLException, BdException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectChangeSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
                cstmt.setObject(params[0], value.getId());
                int stat = 0;
                if(value.getStatus() == 0){
                    stat = 1;
                }            
                cstmt.setInt(params[1], stat );
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,value.getId()+"",mensj);
                connection.commit();
                return true; 
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }          
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE, 3 UPDATE PSWD
     * @param usuario
     * @return
     * @throws SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean save(String user,int operacion, Usuario usuario) throws SQLException, BdException {              
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open(); 
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_user_data(?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Nuevo Usuario:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_user_basic(?, ?, ?, ?, ?, ?)}");
                        mensj = "Actualizando Usuario:";
                        break;
                    case 3:
                        sql.append("{call sp_upd_user_pswd(?, ?)}");
                        mensj = "Actualizando Clave de Usuario:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                switch(operacion){
                    case 1:
                        cstmt.setString("@id_user"      , usuario.getUsername() );
                        cstmt.setString("@nombre1"      , usuario.getNombre1().toUpperCase());
                        cstmt.setString("@nombre2"      , usuario.getNombre2().toUpperCase());
                        cstmt.setString("@apellido1"    , usuario.getApellido1().toUpperCase());
                        cstmt.setString("@apellido2"    , usuario.getApellido2().toUpperCase());
                        cstmt.setString("@clave"        , usuario.getPswd());
                        cstmt.setInt("@id_rol"          , usuario.getRol().getIdRol());
                        break;
                    case 2:
                        cstmt.setString("@id_user"      , usuario.getUsername() );
                        cstmt.setString("@nombre1"      , usuario.getNombre1().toUpperCase());
                        cstmt.setString("@nombre2"      , usuario.getNombre2().toUpperCase());
                        cstmt.setString("@apellido1"    , usuario.getApellido1().toUpperCase());
                        cstmt.setString("@apellido2"    , usuario.getApellido2().toUpperCase());
                        cstmt.setInt("@id_rol"          , usuario.getRol().getIdRol());
                        break;
                    case 3:
                        cstmt.setString("@id_user"      , usuario.getUsername() );
                        cstmt.setString("@clave"        , usuario.getPswd());
                        break;
                }
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,usuario.getUsername(),mensj);
                connection.commit();
                return true; 
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }    
    /***************************************************************************/
    /******************************* SINGLETON *********************************/
    /***************************************************************************/    
    private static class BdHolder {
        private static final BdUsuarios INSTANCE = new BdUsuarios();
    }    
    public static BdUsuarios getInstance() {
        return BdUsuarios.BdHolder.INSTANCE;
    }
}
